﻿using System;
using System.Collections.Generic;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.IO;
using System.Data;

namespace WinServer
{
    public class SqlliteHelper
    {

        public static string connString = "Data Source =./Bussiness.db;";

        public static int Update(string sql)
        {
            SQLiteConnection conn = new SQLiteConnection(connString);
            SQLiteCommand cmd = new SQLiteCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                conn.Close();
                string info = "调用 public static int Update(string sql)出现错误";
                WriteLog(ex.Message + "\r\n" + sql);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();

            }
        }
        #region 单一结果
        public static object SingleResult(string sql)
        {

            SQLiteConnection conn = new SQLiteConnection(connString);
            SQLiteCommand cmd = new SQLiteCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string info = "调用 public static object SingleResult(string sql)出现错误";
                WriteLog(ex.Message);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();

            }
        }


        #endregion
        #region 查询
        public static SQLiteDataReader getReader(string sql)
        {
            SQLiteConnection conn = new SQLiteConnection(connString);
            SQLiteCommand cmd = new SQLiteCommand(sql, conn);
            try
            {
                conn.Open();

                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                string info = "调用public static SQLiteDataReader getReader(string sql)出现错误";
                WriteLog(ex.Message);
                throw new Exception(info);
            }

        }
        #endregion
        #region 编写带参数的sql语句的方法
        public static int Update(string sql, SQLiteParameter[] param)
        {

            SQLiteConnection conn = new SQLiteConnection(connString);
            SQLiteCommand cmd = new SQLiteCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string info = "调用 public static int Update(string sql, SQLiteParameter[] param)出现错误";
                WriteLog(ex.Message + sql);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion
        #region 单一结果
        public static object SingleResult(string sql, SQLiteParameter[] param)
        {
            SQLiteConnection conn = new SQLiteConnection(connString);
            SQLiteCommand cmd = new SQLiteCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string info = "调用public static object SingleResult(string sql, SQLiteParameter[] param)";
                WriteLog(ex.Message);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();

            }
        }
        #endregion
        #region 查询
        public static SQLiteDataReader getReader(string sql, SQLiteParameter[] param)
        {
            SQLiteConnection conn = new SQLiteConnection(connString);
            SQLiteCommand cmd = new SQLiteCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                string info = "调用public static SQLiteDataReader getReader(string sql, SQLiteParameter[] param)出现错误";
                WriteLog(ex.Message);
                throw new Exception(info);
            }

        }
        #endregion
        #region 存储过程

        public static int UpdateByProcedure(string spname, SQLiteParameter[] param)
        {
            SQLiteConnection conn = new SQLiteConnection(connString);
            SQLiteCommand cmd = new SQLiteCommand(spname, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                cmd.CommandType = CommandType.StoredProcedure;
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string info = "调用 UpdateByProcedure出现错误";
                WriteLog(ex.Message);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();

            }
        }

        public static void ExcuteNoQuery()
        {
            using (var connection = new SQLiteConnection(connString))
            {
                connection.Open();

                // 创建表
                string createTableSql = @"
                CREATE TABLE IF NOT EXISTS byte_data (
                    id INTEGER PRIMARY KEY,
                    data BLOB
                )";
                using (var command = new SQLiteCommand(createTableSql, connection))
                {
                    command.ExecuteNonQuery();
                }

                // 插入byte数组数据
                byte[] byteData = new byte[] { 0, 1, 2, 3, 4, 5 };
                string insertSql = @"INSERT INTO byte_data (data) VALUES (@data)";
                using (var command = new SQLiteCommand(insertSql, connection))
                {
                    command.Parameters.AddWithValue("@data", byteData);
                    command.ExecuteNonQuery();
                }

                //// 检索byte数组数据
                //string selectSql = "SELECT data FROM byte_data";
                //using (var command = new SQLiteCommand(selectSql, connection))
                //using (SQLiteDataReader reader = command.ExecuteReader())
                //{
                //    while (reader.Read())
                //    {
                //        byte[] retrievedData = (byte[])reader["data"];
                //        Console.WriteLine($"Retrieved byte array: {BitConverter.ToString(retrievedData)}");
                //    }
                //}
            }
        }

        public void InsertRecordData(byte[] data)
        {
        }
        #endregion
        #region
        public static object SingleResultByProcedure(string spname, SQLiteParameter[] param)
        {
            SQLiteConnection conn = new SQLiteConnection(connString);
            SQLiteCommand cmd = new SQLiteCommand(spname, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                cmd.CommandType = CommandType.StoredProcedure;
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string info = "调用public static object SingleResult(string sql, SQLiteParameter[] param)";
                WriteLog(ex.Message);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();

            }
        }
        #endregion
        #region 查询
        public static SQLiteDataReader getReaderByProcedure(string spname, SQLiteParameter[] param)
        {
            SQLiteConnection conn = new SQLiteConnection(connString);
            SQLiteCommand cmd = new SQLiteCommand(spname, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                cmd.CommandType = CommandType.StoredProcedure;
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                string info = "调用public static SQLiteDataReader getReader(string sql, SQLiteParameter[] param)出现错误";
                WriteLog(ex.Message);
                throw new Exception(info);
            }

        }
        #endregion
        ///
        /// 系统日志
        ///
        ///
        public static void WriteLog(string log)
        {
            FileStream fs = new FileStream(@"./日志/mysqlhelp.log", FileMode.Append);
            StreamWriter sw = new StreamWriter(fs);
            sw.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "错误信息：" + log);
            sw.Close();
            fs.Close();
        }
    }
}